{"text_format":"1","workbook":"xlsx","n_sheets":11,"sheets":["Introduction","Quick bond review","YTM and duration","Graph","Bond portfolio","FRN & Swap rates","modele","Getting the spot rates","PCA","Homework","Immunisation"],"not_rendered":{"charts":1,"images":34,"macros":1,"external_links":0,"defined_names":9,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Introduction","index":0,"n_rows":17,"n_cols":10,"rows_kept":17,"cut":false,"hidden":false,"merged":[[3,5,3,7],[4,5,4,7],[5,5,5,7],[4,9,13,16]]}
[null,null,null,null,null,null,{"$f":" RIGHT(CELL(\"filename\", C1), LEN(CELL(\"filename\", C1)) - SEARCH(\"]\", CELL(\"filename\", C1)))","v":"Introduction"}]
[null,null,null,null,null,null,{"$f":"MID(MID(CELL(\"filename\",$A$1),SEARCH(\"[\",CELL(\"filename\",$A$1))+1,SEARCH(\".\",CELL(\"filename\",$A$1))-SEARCH(\"[\",CELL(\"filename\",$A$1))-1),13,100) & \" by Prof. Hugues Pirotte\" & \" (version \" &2011&\")\"","v":"Fixed Income and Risk Management (FI) by Prof. Hugues Pirotte (version 2011)"}]
[]
[null,null,null,null,null,"Below is a list of the worksheets",null,null,null,"Comment"]
[null,null,null,null,null,"of the file, by order of appearance:"]
[null,null,null,null,null,"(CTRL+h = back to \"Intro\")"]
[null,null,null,null,1,"Introduction"]
[null,null,null,null,2,"Quick bond review"]
[null,null,null,null,3,"YTM and duration"]
[null,null,null,null,4,"Graph"]
[null,null,null,null,5,"Bond portfolio"]
[null,null,null,null,6,"FRN & Swap rates"]
[null,null,null,null,7,"modele"]
[null,null,null,null,8,"Getting the spot rates"]
[null,null,null,null,9,"PCA"]
[null,null,null,null,10,"Homework"]
[null,null,null,null,11,"Immunisation"," "]

{"sheet":"Quick bond review","index":1,"n_rows":91,"n_cols":15,"rows_kept":91,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,null,{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"Quick bond review"}]
[null,null,null,null,null,{"$f":"MID(MID(CELL(\"filename\",$A$1),SEARCH(\"[\",CELL(\"filename\",$A$1))+1,SEARCH(\".\",CELL(\"filename\",$A$1))-SEARCH(\"[\",CELL(\"filename\",$A$1))-1),13,100) & \" by Prof. Hugues Pirotte\" & \" (version \" &2011&\")\"","v":"Fixed Income and Risk Management (FI) by Prof. Hugues Pirotte (version 2011)"}]
[]
[null,1,"Simple bond pricing"]
[null,null,null,"Here below you find a 5y straight bond, with the possibility to change the coupon rate"]
[]
[null,null,null,null,null,"coupon",5]
[]
[null,null,null,null,"Year","Spot rates","Payments","PV(payments)","Price"]
[null,null,null,null,0,null,null,null,{"$f":"SUM(H10:H15)","v":105.0150031714723},{"$f":"IF(I10=100,\"at par\",IF(I10<100,\"below par\",\"above par\"))","v":"above par"}]
[null,null,null,null,1,0.02,{"$f":"$G$7","v":5},{"$f":"G11/(1+F11)^E11","v":4.9019607843137258}]
[null,null,null,null,2,2.5000000000000001E-2,{"$f":"$G$7","v":5},{"$f":"G12/(1+F12)^E12","v":4.7590719809637125}]
[null,null,null,null,3,2.75E-2,{"$f":"$G$7","v":5},{"$f":"G13/(1+F13)^E13","v":4.6091889568845081}]
[null,null,null,null,4,0.03,{"$f":"$G$7","v":5},{"$f":"G14/(1+F14)^E14","v":4.4424352395784448}]
[null,null,null,null,5,0.04,{"$f":"$G$7+100","v":105},{"$f":"G15/(1+F15)^E15","v":86.302346209731908}]
[]
[null,2,"Yield-to-maturity"]
[]
[null,null,null,null,"Investment",null,"Using the IRR on the corresponding bond investment is the same"]
[null,null,null,0,{"$f":"-I10","v":-105.0150031714723},null,"as asking Excel to \"Goal Seek\" the single rate so that we obtain"]
[null,null,null,1,{"$f":"G11","v":5},null,"the same price."]
[null,null,null,2,{"$f":"G12","v":5},null,"coupon",5]
[null,null,null,3,{"$f":"G13","v":5},null,"flat rate",3.8773723250235273E-2]
[null,null,null,4,{"$f":"G14","v":5}]
[null,null,null,5,{"$f":"G15","v":105},null,"Year","Spot rates","Payments","PV(payments)","Price"]
[null,null,null,"YTM=IRR",{"$f":"IRR(E20:E25)","v":3.8773721899287406E-2},null,0,null,null,null,{"$f":"SUM(J26:J31)","v":105.01500254878462},{"$f":"K26-I10","v":-6.226876791970426E-7}]
[null,null,null,null,null,null,1,{"$f":"$H$23","v":3.8773723250235273E-2},{"$f":"$G$7","v":5},{"$f":"I27/(1+H27)^G27","v":4.8133678086844771}]
[null,null,null,null,null,null,2,{"$f":"$H$23","v":3.8773723250235273E-2},{"$f":"$G$7","v":5},{"$f":"I28/(1+H28)^G28","v":4.6337019323360007}]
[null,null,null,null,null,null,3,{"$f":"$H$23","v":3.8773723250235273E-2},{"$f":"$G$7","v":5},{"$f":"I29/(1+H29)^G29","v":4.4607423432290334}]
[null,null,null,null,null,null,4,{"$f":"$H$23","v":3.8773723250235273E-2},{"$f":"$G$7","v":5},{"$f":"I30/(1+H30)^G30","v":4.2942387195468781}]
[null,null,null,null,null,null,5,{"$f":"$H$23","v":3.8773723250235273E-2},{"$f":"$G$7+100","v":105},{"$f":"I31/(1+H31)^G31","v":86.81295174498824}]
[]
[]
[null,3,"Risks?"]
[null,null,null,1,"Bond price risk"]
[null,null,null,2,"Reinvestment risk"]
[]
[null,4,"Forward rates"]
[]
[]
[null,null,null,null,"Year","Spot rates"]
[null,null,null,null,0]
[null,null,null,null,1,0.02]
[null,null,null,null,2,2.5000000000000001E-2]
[null,null,null,null,3,0.03]
[null,null,null,null,4,3.5000000000000003E-2]
[null,null,null,null,5,0.05,{"$f":"F47+E46*(F47-F46)","v":0.11},{"$f":"(1+F47)^E47/(1+F46)^E46-1","v":0.11220564799561483}]
[null,null,null,null,null,null,"without","with"]
[null,null,null,null,null,null,"compounding"]
[]
[null,null,null,"In this case, we can see that the cost of the 5th year of fixing is not 5% but 11%. "]
[null,null,null,"A treasurer would probably see this as the cost of fixing a 5th year  and being riskless (if the projects are also for 5 years)."]
[null,null,null,"But at the same time, he/she will see the firm as an ongoing concern. So, over 20 years, this means rollovering 4 times a"]
[null,null,null,"5y loan or 5 times a 4y loan… He/she may see this as too expensive and prefer the 4y loan and rollover it…"]
[null,null,null,"Is that a good analysis? Well, in terms of pure myopic cost analysis, but not in fundamental finance terms."]
[null,null,null,"Interest rate theory would tell you that if this is a market zero-coupon curve, then this is what the market offers/demands,"]
[null,null,null,"and therefore, the 11% is not far from the expectation of the 1y spot rate in 4 years from now (\"not far\", because there are "]
[null,null,null,"several variations on the expectations hypothesis)."]
[]
[]
[null,5,"Duration…why?"]
[null,null,null,"Example of a bank…"]
[null,null,null,null,null,null,null,null,null,null,"Asset",null,null,"Liabilities"]
[null,null,null,null,"Assets",null,"Liabilities",null,null,0.5,"ST loans",0.5,0.4,"Deposits",{"$f":"3/12","v":0.25}]
[null,null,null,null,"Loans",null,"Deposits",null,null,{"$f":"1-J64","v":0.5},"Mortgages",20,0.2,"Interbank ",{"$f":"6/12","v":0.5}]
[null,null,null,null,"Mortgages",null,"Interbank loans",null,null,null,null,null,0.32,"Bonds",5]
[null,null,null,null,"etc…",null,"Debt",null,null,null,null,null,0.08,"Equity",100]
[null,null,null,null,null,null,"Equity",null,null,null,null,null,{"$f":"SUM(M64:M67)","v":1.0000000000000002}]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUMPRODUCT(J64:J68,L64:L68)","v":10.25},null,null,{"$f":"SUMPRODUCT(M64:M68,O64:O68)","v":9.8000000000000007}]
[]
[null,null,null,"Duration…meanings?"]
[null,null,null,1,"Sensitivity to Irs --> first derivative of a bond price to changes in Irs."]
[null,null,null,null,"Important for \"market values\""]
[null,null,null,null,"Important for the interest margin"]
[null,null,null,null,"ex: for a Savings & Loans --> the volatility of the interest margin that matters."]
[null,null,null,2,"\"Effective maturity\" of the borrowing/lending --> a kind of a discounted payback period."]
[null,null,null,3,"This bond has the behavior of a 0-coupon bond lasting Duration-years."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,6,"Convexity"]
[null,null,null,null,"It is just a second derivative that can be used in a Taylor's expansion."]

{"sheet":"YTM and duration","index":2,"n_rows":25,"n_cols":17,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,null,{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"YTM and duration"}]
[null,null,null,null,null,{"$f":"MID(MID(CELL(\"filename\",$A$1),SEARCH(\"[\",CELL(\"filename\",$A$1))+1,SEARCH(\".\",CELL(\"filename\",$A$1))-SEARCH(\"[\",CELL(\"filename\",$A$1))-1),13,100) & \" by Prof. Hugues Pirotte\" & \" (version \" &2011&\")\"","v":"Fixed Income and Risk Management (FI) by Prof. Hugues Pirotte (version 2011)"}]
[]
[null,"C","Yield-to-maturity and reinvestment"]
[null,null,null,null,null,null,null,null,null,null,"reinvestment rate"]
[null,null,null,null,null,null,null,null,null,null,0.11]
[null,null,null,null,"coupon",10]
[null,null,null,null,"maturity",5,null,"Weight of PV",null,"Investment","Reinvestment"]
[null,null,null,"Year","spot rates","Cash flow","PV","in total price",null,"Profile","of CFs","Money","Total","Annual"]
[null,null,null,0,null,null,null,null,null,{"$f":"-G21","v":-89.779717376476242},null,"at end","return","return",null,null,{"$f":"J10","v":-89.779717376476242}]
[null,null,null,1,0.12,{"$f":"IF($D11<=F$8,F$7,0)+IF($D11=F$8,100,0)","v":10},{"$f":"F11/(1+E11)^D11","v":8.928571428571427},{"$f":"G11/$G$21","v":9.9449760920174823E-2},null,{"$f":"F11","v":10},{"$f":"J11*(1+$K$6)^($F$8-D11)","v":15.180704100000003},{"$f":"SUM(K11:K20)","v":162.27801410000001},{"$f":"L11/G21-1","v":0.80751308694272539},{"$f":"(1+M11)^(1/F8)-1","v":0.12568347337899155},null,{"$f":"J11","v":10},{"$f":"Q10+P11","v":-79.779717376476242}]
[null,null,null,2,{"$f":"E11+0.25%","v":0.1225},{"$f":"IF($D12<=F$8,F$7,0)+IF($D12=F$8,100,0)","v":10},{"$f":"F12/(1+E12)^D12","v":7.9364685691043189},{"$f":"G12/$G$21","v":8.8399348995765542E-2},null,{"$f":"F12","v":10},{"$f":"J12*(1+$K$6)^($F$8-D12)","v":13.676310000000003},null,null,null,null,{"$f":"J12","v":10},{"$f":"Q11+P12","v":-69.779717376476242}]
[null,null,null,3,{"$f":"E12+0.25%","v":0.125},{"$f":"IF($D13<=F$8,F$7,0)+IF($D13=F$8,100,0)","v":10},{"$f":"F13/(1+E13)^D13","v":7.0233196159122082},{"$f":"G13/$G$21","v":7.8228355146865644E-2},null,{"$f":"F13","v":10},{"$f":"J13*(1+$K$6)^($F$8-D13)","v":12.321000000000002},null,null,null,null,{"$f":"J13","v":10},{"$f":"Q12+P13","v":-59.779717376476242}]
[null,null,null,4,{"$f":"E13+0.25%","v":0.1275},{"$f":"IF($D14<=F$8,F$7,0)+IF($D14=F$8,100,0)","v":10},{"$f":"F14/(1+E14)^D14","v":6.1877648029489443},{"$f":"G14/$G$21","v":6.8921633791757073E-2},null,{"$f":"F14","v":10},{"$f":"J14*(1+$K$6)^($F$8-D14)","v":11.100000000000001},null,null,null,null,{"$f":"J14","v":10},{"$f":"Q13+P14","v":-49.779717376476242}]
[null,null,null,5,{"$f":"E14+0.25%","v":0.13},{"$f":"IF($D15<=F$8,F$7,0)+IF($D15=F$8,100,0)","v":110},{"$f":"F15/(1+E15)^D15","v":59.703592959939343},{"$f":"G15/$G$21","v":0.66500090114543697},null,{"$f":"F15","v":110},{"$f":"J15*(1+$K$6)^($F$8-D15)","v":110},null,null,null,null,{"$f":"J15","v":110},{"$f":"Q14+P15","v":60.220282623523758}]
[null,null,null,6,{"$f":"E15+0.25%","v":0.13250000000000001},{"$f":"IF($D16<=F$8,F$7,0)+IF($D16=F$8,100,0)","v":0},{"$f":"F16/(1+E16)^D16","v":0},{"$f":"G16/$G$21","v":0},null,{"$f":"F16","v":0},{"$f":"J16*(1+$K$6)^($F$8-D16)","v":0},null,null,null,null,{"$f":"J16","v":0},{"$f":"Q15+P16","v":60.220282623523758}]
[null,null,null,7,{"$f":"E16+0.25%","v":0.13500000000000001},{"$f":"IF($D17<=F$8,F$7,0)+IF($D17=F$8,100,0)","v":0},{"$f":"F17/(1+E17)^D17","v":0},{"$f":"G17/$G$21","v":0},null,{"$f":"F17","v":0},{"$f":"J17*(1+$K$6)^($F$8-D17)","v":0}]
[null,null,null,8,{"$f":"E17+0.25%","v":0.13750000000000001},{"$f":"IF($D18<=F$8,F$7,0)+IF($D18=F$8,100,0)","v":0},{"$f":"F18/(1+E18)^D18","v":0},{"$f":"G18/$G$21","v":0},null,{"$f":"F18","v":0},{"$f":"J18*(1+$K$6)^($F$8-D18)","v":0}]
[null,null,null,9,{"$f":"E18+0.25%","v":0.14000000000000001},{"$f":"IF($D19<=F$8,F$7,0)+IF($D19=F$8,100,0)","v":0},{"$f":"F19/(1+E19)^D19","v":0},{"$f":"G19/$G$21","v":0},null,{"$f":"F19","v":0},{"$f":"J19*(1+$K$6)^($F$8-D19)","v":0}]
[null,null,null,10,{"$f":"E19+0.25%","v":0.14250000000000002},{"$f":"IF($D20<=F$8,F$7,0)+IF($D20=F$8,100,0)","v":0},{"$f":"F20/(1+E20)^D20","v":0},{"$f":"G20/$G$21","v":0},null,{"$f":"F20","v":0},{"$f":"J20*(1+$K$6)^($F$8-D20)","v":0}]
[null,null,null,null,null,null,{"$f":"SUM(G11:G20)","v":89.779717376476242},null,null,{"$f":"IRR(J10:J20)","v":0.12898588360035768},"YIELD-TO-MATURITY OF THE BOND"]
[]
[null,null,null,null,null,"Duration",{"$f":"SUMPRODUCT(H11:H20,D11:D20)","v":4.1116245652465162},"Thanks to the duration, we know that this 6y bond has the same sensitivity to interest rates than an otherwise equivalent"]
[null,null,null,null,null,null,null,"0-coupon bond of 4.11 years. That also means that, it is approximately right to say that a 6Y bond does not present a reinvestment"]
[null,null,null,null,null,null,null,"risk if it is considered as having a real duration of 4.11 years, and not of 6 years."]

{"sheet":"Graph","index":3,"n_rows":38,"n_cols":22,"rows_kept":38,"cut":false,"hidden":false,"merged":[]}
[]
[null,{"$f":" RIGHT(CELL(\"filename\", C1), LEN(CELL(\"filename\", C1)) - SEARCH(\"]\", CELL(\"filename\", C1)))","v":"Graph"}]
[]
[]
[null,null,"coupon",12]
[null,null,"maturity",6]
[null,"Year","spot rates","Bond 1","PV",null,null,"Maturities"]
[null,1,0.12,{"$f":"IF($B8<=D$6,D$5,0)+IF($B8=D$6,100,0)","v":12},{"$f":"D8/(1+C8)^B8","v":10.714285714285714},null,{"$f":"E23","v":99.999999999999972},1,2,3,4,5,6,7,8,9,10,11,12,13,14,15]
[null,2,{"$f":"$C$8","v":0.12},{"$f":"IF($B9<=D$6,D$5,0)+IF($B9=D$6,100,0)","v":12},{"$f":"D9/(1+C9)^B9","v":9.566326530612244},"IR",0.01,110.89108910891089,121.67434565238702,132.3508372795911,142.92162106890208,153.38774363257633,163.75024122037257,174.0101398221511,184.1684552694565,194.22619333609555,204.18434983771834,214.04391073041421,223.80585220833092,233.47114080032762,243.04073346567088,252.51557768878308]
[null,3,{"$f":"$C$8","v":0.12},{"$f":"IF($B10<=D$6,D$5,0)+IF($B10=D$6,100,0)","v":12},{"$f":"D10/(1+C10)^B10","v":8.5413629737609309},null,0.02,109.80392156862744,119.4156093810073,128.83883272647776,138.07728698674291,147.13459508504206,156.01430890690398,164.71991069304312,173.25481440494423,181.62236706367082,189.82585006242238,197.86848045335529,205.75341220917181,213.48373745997239,221.06248770585529,228.49263500574051]
[null,4,{"$f":"$C$8","v":0.12},{"$f":"IF($B11<=D$6,D$5,0)+IF($B11=D$6,100,0)","v":12},{"$f":"D11/(1+C11)^B11","v":7.6262169408579741},null,0.03,108.7378640776699,117.2212272598737,125.45750219405213,133.45388562529334,141.21736468475081,148.75472299490369,156.07254659699387,163.17722970581931,170.07498029691197,176.77182553098248,183.27361702037135,189.58603594210811,195.71459800204673,201.66465825441429,207.44141578098473]
[null,5,{"$f":"$C$8","v":0.12},{"$f":"IF($B12<=D$6,D$5,0)+IF($B12=D$6,100,0)","v":12},{"$f":"D12/(1+C12)^B12","v":6.8091222686231907},null,0.04,107.69230769230769,115.08875739644969,122.20072826581702,129.03916179405479,135.61457864812962,141.93709485397076,148.01643735958729,153.86195899960313,159.48265288423377,164.88716623484015,170.08381368734632,175.08059008398681,179.88518277306423,184.50498343563868,188.94709945734488]
[null,6,{"$f":"$C$8","v":0.12},{"$f":"IF($B13<=D$6,D$5,0)+IF($B13=D$6,100,0)","v":112},{"$f":"D13/(1+C13)^B13","v":56.742685571859916},null,0.05,106.66666666666666,113.01587301587301,119.06273620559334,124.82165352913651,130.30633669441573,135.52984447087212,140.50461378178295,145.24248931598379,149.75475172950834,154.05214450429366,158.1448995278987,162.04276145514166,165.75501090965869,169.29048658062734,172.65760626726413]
[null,7,{"$f":"$C$8","v":0.12},{"$f":"IF($B14<=D$6,D$5,0)+IF($B14=D$6,100,0)","v":0},{"$f":"D14/(1+C14)^B14","v":0},null,0.06,105.66037735849056,111.00035599857598,116.03807169676979,120.79063367619793,125.27418271339425,129.50394595603231,133.49428863776632,137.25876286581729,140.81015364699743,144.16052230848811,147.32124746083784,150.30306364229983,153.11609777575455,155.7699035620326,158.27349392644584]
[null,8,{"$f":"$C$8","v":0.12},{"$f":"IF($B15<=D$6,D$5,0)+IF($B15=D$6,100,0)","v":0},{"$f":"D15/(1+C15)^B15","v":0},null,7.0000000000000007E-2,104.67289719626167,109.04009083762774,113.121580222082,116.93605628231963,120.50098717973796,123.83269829882053,126.94644700824348,129.85649253106868,132.57616124398942,135.117907704663,137.49337168660094,139.71343148280459,141.7882537222473,143.7273399273339,145.53957002554571]
[null,9,{"$f":"$C$8","v":0.12},{"$f":"IF($B16<=D$6,D$5,0)+IF($B16=D$6,100,0)","v":0},{"$f":"D16/(1+C16)^B16","v":0},null,0.08,103.7037037037037,107.13305898491083,110.30838794899151,113.2485073601773,115.97084014831232,118.49151865584473,120.82548023689326,122.98655577490118,124.987551643427,126.84032559576573,128.55585703311641,130.14431206770035,131.61510376638921,132.97694793184186,134.2379147517054]
[null,10,{"$f":"$C$8","v":0.12},{"$f":"IF($B17<=D$6,D$5,0)+IF($B17=D$6,100,0)","v":0},{"$f":"D17/(1+C17)^B17","v":0},null,0.09,102.75229357798165,105.27733355778132,107.5938839979645,109.7191596311601,111.66895379005513,113.45775577069278,115.09885850522272,116.60445734424103,117.98574068278992,119.25297310347699,120.41557165456604,121.48217582987709,122.46071177052943,123.35845116562331,124.18206528956267]
[null,11,{"$f":"$C$8","v":0.12},{"$f":"IF($B18<=D$6,D$5,0)+IF($B18=D$6,100,0)","v":0},{"$f":"D18/(1+C18)^B18","v":0},null,0.1,101.81818181818181,103.47107438016528,104.97370398196841,106.33973089269855,107.58157353881687,108.71052139892441,109.73683763538583,110.66985239580529,111.51804763255026,112.2891342114093,112.99012201037209,113.6273836457928,114.20671240526619,114.73337491387832,115.21215901261667]
[null,12,{"$f":"$C$8","v":0.12},{"$f":"IF($B19<=D$6,D$5,0)+IF($B19=D$6,100,0)","v":0},{"$f":"D19/(1+C19)^B19","v":0},null,0.11,100.90090090090089,101.71252333414493,102.44371471544589,103.10244568959088,103.69589701764944,104.23053785373823,104.71219626462903,105.14612276092704,105.5370475323667,105.88923201114116,106.2065153253524,106.49235614896611,106.74987040447395,106.9818652292558,107.19086957590612]
[null,13,{"$f":"$C$8","v":0.12},{"$f":"IF($B20<=D$6,D$5,0)+IF($B20=D$6,100,0)","v":0},{"$f":"D20/(1+C20)^B20","v":0},null,0.12,99.999999999999986,99.999999999999986,99.999999999999972,99.999999999999986,99.999999999999972,99.999999999999972,99.999999999999972,99.999999999999972,99.999999999999972,99.999999999999972,99.999999999999957,99.999999999999972,99.999999999999943,99.999999999999943,99.999999999999943]
[null,14,{"$f":"$C$8","v":0.12},{"$f":"IF($B21<=D$6,D$5,0)+IF($B21=D$6,100,0)","v":0},{"$f":"D21/(1+C21)^B21","v":0},null,0.13,99.115044247787623,98.331897564413836,97.63884740213615,97.02552867445678,96.482768738457338,96.002450211024211,95.57738956727809,95.201229705555846,94.868344872173324,94.573756524047212,94.313058870838248,94.082352983042711,93.878188480568781,93.697511929706906,93.53762117673179]
[null,15,{"$f":"$C$8","v":0.12},{"$f":"IF($B22<=D$6,D$5,0)+IF($B22=D$6,100,0)","v":0},{"$f":"D22/(1+C22)^B22","v":0},null,0.14000000000000001,98.245614035087712,96.706678978147096,95.356735945743054,94.172575391002681,93.133838062283047,92.222664966914934,91.423390321855194,90.722272212153655,90.107256326450567,89.567768707412768,89.094533953870837,88.679415749009479,88.315276972815326,87.995856993697643,87.715664029559335]
[null,null,null,null,{"$f":"SUM(E8:E22)","v":99.999999999999972},null,0.15,97.391304347826093,95.122873345935744,93.150324648639796,91.435064911860678,89.943534705965817,88.646551918231154,87.518740798461891,86.538035476923397,85.685248240802963,84.943694122437364,84.298864454293366,83.73814300373337,83.250559133681193,82.826573159722784,82.457889704106776]
[null,null,null,null,null,null,0.16,96.551724137931046,93.579072532699172,91.01644183853378,88.807277447011884,86.902825385355072,85.261056366685409,83.845738247142606,82.625636419950524,81.57382449995734,80.667090086170134,79.885422488077694,79.211571110411811,78.63066475035501,78.129883405478466,77.698175349550411]
[null,null,null,null,null,null,0.17,95.726495726495727,92.073927971363887,88.952075189199917,86.283824948034123,84.003269186353947,82.054076227652956,80.38809933987433,78.964187469978071,77.747168777759043,76.706981861332522,75.817933214814119,75.058062576764215,74.408600492960886,73.853504694838364,73.379063841742195]
[null,null,null,null,null,null,0.18,94.915254237288138,90.606147658718754,86.954362422643015,83.859629171731385,81.236973874348649,79.014384639278518,77.130834440066536,75.534605457683512,74.181869031935179,73.035482230453553,72.063967991909792,71.240650840601518,70.542924441187736,69.951630882362494,69.450534646069912]
[null,null,null,null,null,null,0.19,94.117647058823536,89.174493326742464,85.020582627514671,81.529901367659392,78.596555771142349,76.131559471548201,74.06013400970437,72.319440344289376,70.856672558226364,69.627455931282668,68.594500782590472,67.72647124587435,66.997034660398612,66.384062739830767,65.868960285572072]
[null,null,null,null,null,null,0.2,93.333333333333343,87.777777777777786,83.148148148148152,79.290123456790127,76.075102880658449,73.395919067215374,71.16326588934615,69.30272157445512,67.752267978712609,66.460223315593851,65.383519429661547,64.48626619138463,63.738555159487191,63.115462632905995,62.596218860754995]
[null,null,null,null,null,null,0.21,92.561983471074385,86.414862372788747,81.334597002304747,77.136030580417156,73.666140975551372,70.798463616158159,68.42848232740343,66.469820105292101,64.851090996109178,63.513298343891883,62.407684581728837,61.493954199775899,60.738805123781738,60.114714978332017,59.598937998621501]
[null,null,null,null,null,null,0.22,91.803278688524586,85.084654662725086,79.577585789118928,75.063594909113874,71.363602384519567,68.330821626655393,65.844935759553593,63.807324393076719,62.137151141866163,60.768156673660791,59.646030060377704,58.726254147850582,57.972339465451299,57.354376611025657,56.847849681168576]
[null,null,null,null,null,null,0.23,91.056910569105696,83.78610615374447,77.874883051824781,73.069010611239648,69.161797244910289,65.985201012122189,63.402602448879833,61.302928820227507,59.595877089615861,58.208030154159239,57.079699312324593,56.162357164491539,55.416550540237033,54.810203691249619,54.317238773373674]
[null,null,null,null,null,null,0.24,90.322580645161295,82.518210197710715,76.224363062669937,71.148679889249934,67.055387007459629,63.754344360854532,61.092213194237523,58.945333221159288,57.21397840416072,55.81772451948445,54.69171332216488,53.783639775939413,53.051322399951133,52.460743870928333,51.984470863651872]
[null,null,null,null,null,null,0.25,89.6,81.28,74.623999999999995,69.299199999999999,65.039359999999988,61.631488000000004,58.905190399999995,56.724152320000002,54.979321855999999,53.5834574848,52.466765987839999,51.573412790272002,50.858730232217596,50.286984185774081,49.829587348619263]
[null,null,null,null,null,null,0.26,88.888888888888886,80.070546737213391,73.071862489851895,67.517351182422146,63.109008874938212,59.610324503919216,56.833590876126365,54.629834028671709,52.880820657675962,51.492714807679334,50.391043498158204,49.516701189014448,48.822778721440038,48.272046604317488,47.834957622474192]
[null,null,null,null,null,null,0.27,88.188976377952756,78.88895777791555,71.566108486547691,65.800085422478489,61.259909781479124,57.684968331873328,54.870053804624661,52.653585672932806,50.908335175537644,49.534122185462714,48.452064712962766,47.600050955088797,46.929173980384881,46.400924394003844,45.984979837798299]
[null,null,null,null,null,null,0.28000000000000003,87.5,77.734375,70.10498046875,64.144515991210938,59.487903118133545,55.849924311041832,53.007753368001431,50.787307318751118,49.052583842774311,47.697331127167431,46.638539943099559,45.81135933054653,45.165124476989476,44.660253497648029,44.265823045037529]
[null,null,null,null,null,null,0.28999999999999998,86.821705426356587,76.605973198726034,68.686800929244981,62.54790769708913,57.789075734177622,54.100058708664818,51.240355588112266,49.023531463652915,47.305063150118535,45.972917170634517,44.940245868708928,44.139725479619322,43.519167038464587,43.038113983305884,42.66520463822161]
[null,null,null,null,null,null,0.3,86.153846153846146,75.50295857988165,67.309968138370493,61.007667798746539,56.15974446057426,52.430572661980193,49.561978970753991,47.355368439041527,45.657975722339636,44.352289017184333,43.347914628603334,42.575318945079488,41.981014573138062,41.523857363952359,41.172197972271036]

{"sheet":"Bond portfolio","index":4,"n_rows":65,"n_cols":15,"rows_kept":65,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,null,{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"Bond portfolio"}]
[null,null,null,null,null,{"$f":"MID(MID(CELL(\"filename\",$A$1),SEARCH(\"[\",CELL(\"filename\",$A$1))+1,SEARCH(\".\",CELL(\"filename\",$A$1))-SEARCH(\"[\",CELL(\"filename\",$A$1))-1),13,100) & \" by Prof. Hugues Pirotte\" & \" (version \" &2011&\")\"","v":"Fixed Income and Risk Management (FI) by Prof. Hugues Pirotte (version 2011)"}]
[]
[null,1,"Some simple bond portfolio"]
[null,null,null,"As a treasurer, you would look at your bond portfolio more as flows per term than a sum of individual"]
[null,null,null,"bond securities…"]
[null,null,null,null,null,"coupon",4,3,2,1,6]
[null,null,null,null,null,"maturity",2,3,5,4,2]
[null,null,null,null,"Year","spot rates","Bond 1","Bond 2","Bond 3","Bond 4","Bond 5","TOTAL","PV(TOTAL)"]
[null,null,null,null,1,0.13,{"$f":"IF($E10<=G$8,G$7,0)+IF($E10=G$8,100,0)","v":4},{"$f":"IF($E10<=H$8,H$7,0)+IF($E10=H$8,100,0)","v":3},{"$f":"IF($E10<=I$8,I$7,0)+IF($E10=I$8,100,0)","v":2},{"$f":"IF($E10<=J$8,J$7,0)+IF($E10=J$8,100,0)","v":1},{"$f":"IF($E10<=K$8,K$7,0)+IF($E10=K$8,100,0)","v":6},{"$f":"SUM(G10:K10)","v":16},{"$f":"L10/(1+F10)^E10","v":14.159292035398231}]
[null,null,null,null,2,0.13250000000000001,{"$f":"IF($E11<=G$8,G$7,0)+IF($E11=G$8,100,0)","v":104},{"$f":"IF($E11<=H$8,H$7,0)+IF($E11=H$8,100,0)","v":3},{"$f":"IF($E11<=I$8,I$7,0)+IF($E11=I$8,100,0)","v":2},{"$f":"IF($E11<=J$8,J$7,0)+IF($E11=J$8,100,0)","v":1},{"$f":"IF($E11<=K$8,K$7,0)+IF($E11=K$8,100,0)","v":106},{"$f":"SUM(G11:K11)","v":216},{"$f":"L11/(1+F11)^E11","v":168.41366606727775}]
[null,null,null,null,3,0.13550000000000001,{"$f":"IF($E12<=G$8,G$7,0)+IF($E12=G$8,100,0)","v":0},{"$f":"IF($E12<=H$8,H$7,0)+IF($E12=H$8,100,0)","v":103},{"$f":"IF($E12<=I$8,I$7,0)+IF($E12=I$8,100,0)","v":2},{"$f":"IF($E12<=J$8,J$7,0)+IF($E12=J$8,100,0)","v":1},{"$f":"IF($E12<=K$8,K$7,0)+IF($E12=K$8,100,0)","v":0},{"$f":"SUM(G12:K12)","v":106},{"$f":"L12/(1+F12)^E12","v":72.400980811613238}]
[null,null,null,null,4,0.13800000000000001,{"$f":"IF($E13<=G$8,G$7,0)+IF($E13=G$8,100,0)","v":0},{"$f":"IF($E13<=H$8,H$7,0)+IF($E13=H$8,100,0)","v":0},{"$f":"IF($E13<=I$8,I$7,0)+IF($E13=I$8,100,0)","v":2},{"$f":"IF($E13<=J$8,J$7,0)+IF($E13=J$8,100,0)","v":101},{"$f":"IF($E13<=K$8,K$7,0)+IF($E13=K$8,100,0)","v":0},{"$f":"SUM(G13:K13)","v":103},{"$f":"L13/(1+F13)^E13","v":61.414111971438736}]
[null,null,null,null,5,0.14249999999999999,{"$f":"IF($E14<=G$8,G$7,0)+IF($E14=G$8,100,0)","v":0},{"$f":"IF($E14<=H$8,H$7,0)+IF($E14=H$8,100,0)","v":0},{"$f":"IF($E14<=I$8,I$7,0)+IF($E14=I$8,100,0)","v":102},{"$f":"IF($E14<=J$8,J$7,0)+IF($E14=J$8,100,0)","v":0},{"$f":"IF($E14<=K$8,K$7,0)+IF($E14=K$8,100,0)","v":0},{"$f":"SUM(G14:K14)","v":102},{"$f":"L14/(1+F14)^E14","v":52.398532979106996}]
[null,null,null,null,null,null,null,null,null,null,null,"TOTAL",{"$f":"SUM(M10:M14)","v":368.78658386483499},"AV. DURATION",{"$f":"SUMPRODUCT(M10:M14,E10:E14)/M15","v":2.9172392013598625}]
[]
[null,null,null,null,null,"PV","Bond 1","Bond 2","Bond 3","Bond 4","Bond 5"]
[null,null,null,null,null,null,{"$f":"G10/(1+$F10)^$E10","v":3.5398230088495577},{"$f":"H10/(1+$F10)^$E10","v":2.6548672566371683},{"$f":"I10/(1+$F10)^$E10","v":1.7699115044247788},{"$f":"J10/(1+$F10)^$E10","v":0.88495575221238942},{"$f":"K10/(1+$F10)^$E10","v":5.3097345132743365}]
[null,null,null,null,null,null,{"$f":"G11/(1+$F11)^$E11","v":81.088061439800398},{"$f":"H11/(1+$F11)^$E11","v":2.3390786953788574},{"$f":"I11/(1+$F11)^$E11","v":1.5593857969192384},{"$f":"J11/(1+$F11)^$E11","v":0.7796928984596192},{"$f":"K11/(1+$F11)^$E11","v":82.647447236719628}]
[null,null,null,null,null,null,{"$f":"G12/(1+$F12)^$E12","v":0},{"$f":"H12/(1+$F12)^$E12","v":70.351896449020401},{"$f":"I12/(1+$F12)^$E12","v":1.3660562417285516},{"$f":"J12/(1+$F12)^$E12","v":0.68302812086427578},{"$f":"K12/(1+$F12)^$E12","v":0}]
[null,null,null,null,null,null,{"$f":"G13/(1+$F13)^$E13","v":0},{"$f":"H13/(1+$F13)^$E13","v":0},{"$f":"I13/(1+$F13)^$E13","v":1.1925070285716259},{"$f":"J13/(1+$F13)^$E13","v":60.22160494286711},{"$f":"K13/(1+$F13)^$E13","v":0}]
[null,null,null,null,null,null,{"$f":"G14/(1+$F14)^$E14","v":0},{"$f":"H14/(1+$F14)^$E14","v":0},{"$f":"I14/(1+$F14)^$E14","v":52.398532979106996},{"$f":"J14/(1+$F14)^$E14","v":0},{"$f":"K14/(1+$F14)^$E14","v":0}]
[null,null,null,null,null,null,{"$f":"SUM(G18:G22)","v":84.627884448649951},{"$f":"SUM(H18:H22)","v":75.345842401036421},{"$f":"SUM(I18:I22)","v":58.286393550751193},{"$f":"SUM(J18:J22)","v":62.569281714403395},{"$f":"SUM(K18:K22)","v":87.957181749993964},null,"TOTAL",{"$f":"SUM(G23:K23)","v":368.78658386483494}]
[]
[null,null,null,null,null,"Duration",{"$f":"SUMPRODUCT($E$10:$E$14,G18:G22)/G23","v":1.9581719071448913},{"$f":"SUMPRODUCT($E$10:$E$14,H18:H22)/H23","v":2.8984839379996377},{"$f":"SUMPRODUCT($E$10:$E$14,I18:I22)/I23","v":4.7309419580604075},{"$f":"SUMPRODUCT($E$10:$E$14,J18:J22)/J23","v":3.9217302637934335},{"$f":"SUMPRODUCT($E$10:$E$14,K18:K22)/K23","v":1.9396327348417495},null,"AV. DURATION",{"$f":"SUMPRODUCT(G23:K23,G25:K25)/N23","v":2.9172392013598634}]
[]
[null,2,"The duration has many meanings…."]
[null,null,null,"But first…"]
[null,null,"a","If interest rates go up"]
[null,null,null,1,"It's bad for market values, because of the discounting of cash flows.",null,null,null,null,null,null,"--> but I can get rid of that risk by keeping bonds until maturity."]
[null,null,null,2,"It's good for the reinvestment of the coupons!",null,null,null,null,null,null,"--> and this risk can be eliminated if we would have 0-coupon bonds."]
[null,null,"b","And the reverse is true… So, price risk and reinvestment risk are two opposite forces/risks in bonds."]
[]
[null,null,"c","So, the duration has 3 meanings…"]
[null,null,null,1,"The effective \"maturity\" or duration of the bond"]
[null,null,null,2,"It is the maturity of an equivalent 0-coupon bond."]
[null,null,null,3,"The duration is the factor between a change in interest rates"]
[null,null,null,null,"and the change in % of the bond price."]
[]
[]
[]
[null,null,null,4,"The frequency of renewal of the fixing of interest"]
[null,null,null,null,"payments."]
[]
[]
[]
[null,3,"If I am a manager of a pension fund…"]
[]
[null,null,null,null,"Assets",null,"Liabilities"]
[null,null,null,null,"Bond investments",null,"Rents to pensioneers"]
[null,null,null,null,null,null,5]
[null,null,null,null,null,null,10]
[null,null,null,null,null,null,15]
[]
[]
[]
[]
[]
[null,null,null,"A similar duration means:"]
[null,null,null,1,"On average, rents are due at the same term-horizons"]
[null,null,null,null,"that bonds pay back."]
[null,null,null,2,"On average, the sensitivity of our portfolio of bonds"]
[null,null,null,null,"to changes in interest rates is the same as the sensitivity"]
[null,null,null,null,"of the market value of rents."]
[null,null,null,3,"Interest fixing frequencies are similar."]

{"sheet":"FRN & Swap rates","index":5,"n_rows":49,"n_cols":16,"rows_kept":49,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,null,{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"FRN & Swap rates"}]
[null,null,null,null,null,{"$f":"MID(MID(CELL(\"filename\",$A$1),SEARCH(\"[\",CELL(\"filename\",$A$1))+1,SEARCH(\".\",CELL(\"filename\",$A$1))-SEARCH(\"[\",CELL(\"filename\",$A$1))-1),13,100) & \" by Prof. Hugues Pirotte\" & \" (version \" &2011&\")\"","v":"Fixed Income and Risk Management (FI) by Prof. Hugues Pirotte (version 2011)"}]
[]
[null,"C1","A swap…"]
[null,null,null,"...is made of two legs, one \"floating\", one fixed."]
[null,null,null,"The floating one represents a bond with variable coupons…and such a bond is also called a Floating-Rate-Note (FRN)"]
[]
[null,null,null,"FLOATING LEG > FRN",null,null,null,null,null,"FIXED LEG"]
[null,null,null,"paying LIBOR, and assuming the first spot rate of the curve"]
[null,null,null,"here below is LIBOR 12m",null,null,null,null,null,"coupon",3.9012928538066496,"(We can find this coupon rate through a Goal Seek search, to make"]
[null,null,null,null,"Spot",null,null,null,null,null,null,"both legs equal so the value of the swap at inception = 0)"]
[null,null,null,"Year","rates","Payments","PV(payments)","Price",null,"Year","rates","Payments","DF(t)","PV(payments)","Price"]
[null,null,null,0,null,null,null,{"$f":"SUM(G13:G18)","v":100},null,0,null,null,null,null,{"$f":"SUM(N13:N18)","v":100}]
[null,null,null,1,0.02,{"$f":"E14*100","v":2},{"$f":"F14/(1+E14)^D14","v":1.9607843137254901},null,null,1,0.02,{"$f":"$K$10","v":3.9012928538066496},{"$f":"1/(1+K14)^J14","v":0.98039215686274506},{"$f":"L14*M14","v":3.824796915496715}]
[null,null,null,2,2.5000000000000001E-2,{"$f":"((1+E15)^D15/(1+E14)^D14-1)*100","v":3.0024509803921573},{"$f":"F15/(1+E15)^D15","v":2.8577760670002688},null,null,2,2.5000000000000001E-2,{"$f":"$K$10","v":3.9012928538066496},{"$f":"1/(1+K15)^J15","v":0.95181439619274244},{"$f":"L15*M15","v":3.7133067020170372}]
[null,null,null,3,2.75E-2,{"$f":"((1+E16)^D16/(1+E15)^D15-1)*100","v":3.2518307555027048},{"$f":"F16/(1+E16)^D16","v":2.9976604815840946},null,null,3,2.75E-2,{"$f":"$K$10","v":3.9012928538066496},0.92183779137690158,3.5963591878676113]
[null,null,null,4,0.03,{"$f":"((1+E17)^D17/(1+E16)^D16-1)*100","v":3.7536555585644615},{"$f":"F17/(1+E17)^D17","v":3.3350743461212549},null,null,4,0.03,{"$f":"$K$10","v":3.9012928538066496},0.888487047915689,3.4662481707332438]
[null,null,null,5,0.04,{"$f":"((1+E18)^D18/(1+E17)^D17-1)*100+100","v":108.09803455914312},{"$f":"F18/(1+E18)^D18","v":88.848704791568892},null,null,5,0.04,{"$f":"$K$10+100","v":103.90129285380665},0.82192710675935154,85.399289023885387]
[]
[null,null,null,null,"The quotation would appear as \"LIBOR 1y vs. 3.90%\" or \"the 5y swap rate is 3.90%\""]
[]
[null,null,null,"The rate can be found easily by isolating the \"s\" rate in the equation:"]
[]
[null,null,null,"i.e.",{"$f":"(H13-100*M18)/SUM(M14:M18)","v":3.9012928538066496}]
[]
[null,"C2","An amortizing swap"]
[]
[null,null,null,null,"Original Notional",1000000]
[null,null,null,null,"Amortizing?","yes"]
[null,null,null,null,"Years",7]
[]
[null,null,null,"Even if the notional is amortizing through time, the floating leg is still = 100 at a precise date of payment!"]
[]
[null,null,null,"FLOATING LEG > FRN",null,null,null,null,null,null,null,null,null,"coupon",3.0036670413902733E-2,"(obtained with Goal Seek)"]
[]
[null,null,null,"Year","Spot rates","Notionals","Repayments","E(rate)","E(flows)","DFs","Present value","Operation",null,"Fixed flows","PV"]
[null,null,null,0,null,{"$f":"F28","v":1000000},null,null,null,1,null,{"$f":"-K46","v":-1000000.0000000002}]
[null,null,null,1,0.02,{"$f":"IF(D38<=$F$30,IF($F$29=\"yes\",$F$28/$F$30*($F$30-D39+1),$F$28),0)","v":857142.85714285728},{"$f":"F37-F38","v":142857.14285714272},{"$f":"E38","v":0.02},{"$f":"H38*F37+G38","v":162857.14285714272},{"$f":"1/(1+E38)^D38","v":0.98039215686274506},{"$f":"I38/(1+E38)^D38","v":159663.86554621835},{"$f":"I38","v":162857.14285714272},null,{"$f":"($O$34*F37)+G38","v":172893.81327104545},{"$f":"N38*J38","v":169503.73850102496}]
[null,null,null,2,2.5000000000000001E-2,{"$f":"IF(D39<=$F$30,IF($F$29=\"yes\",$F$28/$F$30*($F$30-D40+1),$F$28),0)","v":714285.71428571432},{"$f":"F38-F39","v":142857.14285714296},{"$f":"(1+E39)^D39/(1+E38)^D38-1","v":3.0024509803921573E-2},{"$f":"H39*F38+G39","v":168592.43697479003},{"$f":"1/(1+E39)^D39","v":0.95181439619274244},{"$f":"I39/(1+E39)^D39","v":160468.70860182276},{"$f":"I39","v":168592.43697479003},null,{"$f":"($O$34*F38)+G39","v":168602.86035477387},{"$f":"N39*J39","v":160478.62972494838}]
[null,null,null,3,2.5999999999999999E-2,{"$f":"IF(D40<=$F$30,IF($F$29=\"yes\",$F$28/$F$30*($F$30-D41+1),$F$28),0)","v":571428.57142857148},142857.14285714284,{"$f":"(1+E40)^D40/(1+E39)^D39-1","v":2.8002927781082931E-2},162859.23412934493,{"$f":"1/(1+E40)^D40","v":0.9258868535007081},{"$f":"I40/(1+E40)^D40","v":150789.22385155433},{"$f":"I40","v":162859.23412934493},null,164311.90743850195,152134.23497093417]
[null,null,null,4,2.8000000000000001E-2,{"$f":"IF(D41<=$F$30,IF($F$29=\"yes\",$F$28/$F$30*($F$30-D42+1),$F$28),0)","v":428571.42857142864},142857.14285714284,{"$f":"(1+E41)^D41/(1+E40)^D40-1","v":3.4023422226396871E-2},162299.09841508392,{"$f":"1/(1+E41)^D41","v":0.895421548099118},{"$f":"I41/(1+E41)^D41","v":145326.10995792554},{"$f":"I41","v":162299.09841508392},null,160020.95452223011,143286.21082659383]
[null,null,null,5,0.03,{"$f":"IF(D42<=$F$30,IF($F$29=\"yes\",$F$28/$F$30*($F$30-D43+1),$F$28),0)","v":285714.28571428574},142857.1428571429,{"$f":"(1+E42)^D42/(1+E41)^D41-1","v":3.8038986280877873E-2},159159.56554894769,{"$f":"1/(1+E42)^D42","v":0.86260878438416411},{"$f":"I42/(1+E42)^D42","v":137292.43936128946},{"$f":"I42","v":159159.56554894769},null,155730.00160595836,134334.06737745967]
[null,null,null,6,3.2500000000000001E-2,{"$f":"IF(D43<=$F$30,IF($F$29=\"yes\",$F$28/$F$30*($F$30-D44+1),$F$28),0)","v":142857.14285714287},142857.14285714287,{"$f":"(1+E43)^D43/(1+E42)^D42-1","v":4.5091314515433911E-2},155740.37557583826,{"$f":"1/(1+E43)^D43","v":0.82539082700550481},{"$f":"I43/(1+E43)^D43","v":128546.67739468908},{"$f":"I43","v":155740.37557583826},null,151439.04868968652,124996.40163890726]
[null,null,null,7,3.5499999999999997E-2,{"$f":"IF(D44<=$F$30,IF($F$29=\"yes\",$F$28/$F$30*($F$30-D45+1),$F$28),0)","v":0},142857.14285714287,{"$f":"(1+E44)^D44/(1+E43)^D43-1","v":5.3683939872361641E-2},150526.2771246231,{"$f":"1/(1+E44)^D44","v":0.78333814891919951},{"$f":"I44/(1+E44)^D44","v":117912.9752865007},{"$f":"I44","v":150526.2771246231},null,147148.09577341468,115266.71696013174]
[null,null,null,8,3.7999999999999999E-2,{"$f":"IF(D45<=$F$30,IF($F$29=\"yes\",$F$28/$F$30*($F$30-D46+1),$F$28),0)","v":0},0,{"$f":"(1+E45)^D45/(1+E44)^D44-1","v":5.5669818983533625E-2},0,{"$f":"1/(1+E45)^D45","v":0.74202950092240738},{"$f":"I45/(1+E45)^D45","v":0},{"$f":"I45","v":0},null,0,0]
[null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(K38:K45)","v":1000000.0000000002},null,null,null,{"$f":"SUM(O38:O45)","v":1000000}]
[]
[]
[null,null,null,null,null,null,null,null,"weigthed average",{"$f":"IRR(L37:L45)","v":3.0102980147712621E-2},"(geometric/IRR)"]

{"sheet":"modele","index":6,"n_rows":2,"n_cols":6,"rows_kept":2,"cut":false,"hidden":true,"merged":[]}
[null,null,null,null,null,{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"modele"}]
[null,null,null,null,null,{"$f":"MID(MID(CELL(\"filename\",$A$1),SEARCH(\"[\",CELL(\"filename\",$A$1))+1,SEARCH(\".\",CELL(\"filename\",$A$1))-SEARCH(\"[\",CELL(\"filename\",$A$1))-1),13,100) & \" by Prof. Hugues Pirotte\" & \" (version \" &2011&\")\"","v":"Fixed Income and Risk Management (FI) by Prof. Hugues Pirotte (version 2011)"}]

{"sheet":"Getting the spot rates","index":7,"n_rows":33,"n_cols":12,"rows_kept":33,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,null,{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"Getting the spot rates"}]
[null,null,null,null,null,{"$f":"MID(MID(CELL(\"filename\",$A$1),SEARCH(\"[\",CELL(\"filename\",$A$1))+1,SEARCH(\".\",CELL(\"filename\",$A$1))-SEARCH(\"[\",CELL(\"filename\",$A$1))-1),13,100) & \" by Prof. Hugues Pirotte\" & \" (version \" &2011&\")\"","v":"Fixed Income and Risk Management (FI) by Prof. Hugues Pirotte (version 2011)"}]
[]
[null,1,"The spot rate curve or \"zeor-coupon curve\" or \"curve of zeros\"…"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,2,"In reality, 0-coupon bonds are not very usual…therefore you have to use coupon-bearing bonds"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,3,"What we tend to do, instead is:"]
[null,null,null,"For short term",null,"use Deposit/Libor rates",null,null,"(Money market rates)",null,null,"0-1y"]
[null,null,null,"For medium term",null,"use Forward rates",null,null,null,null,null,"1y-2y-3y"]
[null,null,null,"For long rates",null,"use Swap rates",null,null,null,null,null,"2y and more to 30y"]

{"sheet":"PCA","index":8,"n_rows":25,"n_cols":14,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,null,{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"PCA"}]
[null,null,null,null,null,{"$f":"MID(MID(CELL(\"filename\",$A$1),SEARCH(\"[\",CELL(\"filename\",$A$1))+1,SEARCH(\".\",CELL(\"filename\",$A$1))-SEARCH(\"[\",CELL(\"filename\",$A$1))-1),13,100) & \" by Prof. Hugues Pirotte\" & \" (version \" &2011&\")\"","v":"Fixed Income and Risk Management (FI) by Prof. Hugues Pirotte (version 2011)"}]
[]
[null,1,"Factor loadings for the first 3 factors"]
[]
[null,null,null,null,"Maturity","PC1","PC2","PC3",null,null,"PC1",17.489999999999998,{"$f":"L6^2","v":305.90009999999995},{"$f":"M6/$M$16","v":0.83131103876886625}]
[null,null,null,null,"3m",0.21,-0.56999999999999995,0.5,null,null,"PC2",6.05,{"$f":"L7^2","v":36.602499999999999},{"$f":"M7/$M$16","v":9.9470586301009495E-2}]
[null,null,null,null,"6m",0.26,-0.49,0.23,null,null,"PC3",3.1,{"$f":"L8^2","v":9.6100000000000012},{"$f":"M8/$M$16","v":2.6116039460493172E-2}]
[null,null,null,null,"12m",0.32,-0.32,-0.37,null,null,"PC4",2.17,{"$f":"L9^2","v":4.7088999999999999},{"$f":"M9/$M$16","v":1.2796859335641652E-2}]
[null,null,null,null,"2y",0.35,-0.1,-0.38,null,null,"PC5",1.97,{"$f":"L10^2","v":3.8809},{"$f":"M10/$M$16","v":1.0546694853509671E-2}]
[null,null,null,null,"3y",0.36,-0.02,-0.3,null,null,"PC6",1.69,{"$f":"L11^2","v":2.8560999999999996},{"$f":"M11/$M$16","v":7.7617086683782026E-3}]
[null,null,null,null,"4y",0.36,0.14000000000000001,-0.12,null,null,"PC7",1.27,{"$f":"L12^2","v":1.6129},{"$f":"M12/$M$16","v":4.3832008372351123E-3}]
[null,null,null,null,"5y",0.36,0.17,-0.04,null,null,"PC8",1.24,{"$f":"L13^2","v":1.5376000000000001},{"$f":"M13/$M$16","v":4.1785663136789074E-3}]
[null,null,null,null,"7y",0.34,0.27,0.15,null,null,"PC9",0.8,{"$f":"L14^2","v":0.64000000000000012},{"$f":"M14/$M$16","v":1.7392575707300345E-3}]
[null,null,null,null,"10y",0.31,0.3,0.28000000000000003,null,null,"PC10",0.79,{"$f":"L15^2","v":0.6241000000000001},{"$f":"M15/$M$16","v":1.6960478904572101E-3}]
[null,null,null,null,"30y",0.25,0.33,0.46,null,null,null,null,{"$f":"SUM(M6:M15)","v":367.97310000000004}]
[]
[null,1,"Changes in the portfolio value for a 1-basis point rate move ($ millions)"]
[]
[null,null,null,null,null,"Change","PC1","PC2","PC3"]
[null,null,null,null,"1y",10,{"$f":"SUMPRODUCT($F$21:$F$25,F9:F13)","v":-8.0000000000000293E-2},{"$f":"SUMPRODUCT($F$21:$F$25,G9:G13)","v":-4.08},{"$f":"SUMPRODUCT($F$21:$F$25,H9:H13)","v":-2.0600000000000009}]
[null,null,null,null,"2y",4]
[null,null,null,null,"3y",-8]
[null,null,null,null,"4y",-7]
[null,null,null,null,"5y",2]

{"sheet":"Homework","index":9,"n_rows":22,"n_cols":6,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,null,{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"Homework"}]
[null,null,null,null,null,{"$f":"MID(MID(CELL(\"filename\",$A$1),SEARCH(\"[\",CELL(\"filename\",$A$1))+1,SEARCH(\".\",CELL(\"filename\",$A$1))-SEARCH(\"[\",CELL(\"filename\",$A$1))-1),13,100) & \" by Prof. Hugues Pirotte\" & \" (version \" &2011&\")\"","v":"Fixed Income and Risk Management (FI) by Prof. Hugues Pirotte (version 2011)"}]
[]
[]
[null,1,"The bank has proposed an investment product to a client that lasts"]
[null,null,3,"years"]
[null,null,"There is no intermediary payment. The client invests"]
[null,null,10000,"USD"]
[null,null,"The structure of interest rates at the time of the bank's proposal is  flat and at "]
[null,null,0.12]
[null,null,"So, the client expects to have, exactly after 3 years, a return of 12%"]
[]
[null,null,"There is no ideal 3y 0-coupon bond in which the bank can invest the 10'000 USD."]
[null,null,"But instead, there are two bonds in which the bank can invest to form a portfolio"]
[null,null,"that is aimed at replicating the strategy proposed to the client."]
[null,null,"The two bonds are:"]
[null,null,null,"Maturity","Coupon"]
[null,null,"Bond 1",2,10]
[null,null,"Bond 2",5,10]
[]
[null,"Q","How do you invest the 10'000 in these two bonds?"]
[null,null,"What do you do between day 0 and during the life of the strategy?"]

{"sheet":"Immunisation","index":10,"n_rows":56,"n_cols":28,"rows_kept":56,"cut":false,"hidden":false,"merged":[[16,2,16,4],[16,5,16,7],[25,3,25,4],[25,10,25,11],[25,17,25,18],[25,24,25,25]]}
[null,null,null,null,null,{"$f":" RIGHT(CELL(\"filename\", $A$1), LEN(CELL(\"filename\", $A$1)) - SEARCH(\"]\", CELL(\"filename\", $A$1)))","v":"Immunisation"}]
[null,null,null,null,null,{"$f":"MID(MID(CELL(\"filename\",$A$1),SEARCH(\"[\",CELL(\"filename\",$A$1))+1,SEARCH(\".\",CELL(\"filename\",$A$1))-SEARCH(\"[\",CELL(\"filename\",$A$1))-1),13,100) & \" by Prof. Hugues Pirotte\" & \" (version \" &2011&\")\"","v":"Fixed Income and Risk Management (FI) by Prof. Hugues Pirotte (version 2011)"}]
[]
[null,null,null,null,null,null,"immunisation","= replicating a 0-coupon bond with coupon-paying bonds"]
[]
[null,1,"Commitment with a client",null,null,3,"years investment"]
[null,null,"Current structure of IRs",null,null,0.12,"flat"]
[]
[null,null,"There is no 3 years 0-coupon bond"]
[null,null,"You have two bonds in the market instead"]
[null,null,null,"coupon","maturity"]
[null,"a","A-bond",10,2]
[null,"b","B-bond",10,5]
[]
[null,null,"For the bank, the problem is the following:"]
[]
[null,null,"Assets",null,null,"Liabilities"]
[null,null,"A-bond","?","EUR","3y straight bond",10000,"EUR"]
[null,null,"B-bond","?","EUR"]
[]
[null,null,"duration",3,null,"duration",3]
[null,null,null,null,null,"amount",null,"expected at T=3"]
[]
[null,2,"Solution"]
[]
[null,null,null,"Year 0",null,"rate",{"$f":"F7","v":0.12},null,null,null,"Year 1",null,"rate",0.12,null,null,null,"Year 2",null,"rate",0.12,null,null,null,"Year 3",null,"rate",0.12]
[]
[null,null,null,"A-bond",null,"B-bond",null,null,null,null,"A-bond",null,"B-bond",null,null,null,null,"A-bond",null,"B-bond",null,null,null,null,"A-bond",null,"B-bond"]
[null,null,null,"CFs","PV(CFs)","CFs","PV(CFs)",null,null,null,"CFs","PV(CFs)","CFs","PV(CFs)",null,null,null,"CFs","PV(CFs)","CFs","PV(CFs)",null,null,null,"CFs","PV(CFs)","CFs","PV(CFs)"]
[null,null,0,null,{"$f":"SUM(E31:E36)","v":96.61989795918366},null,{"$f":"SUM(G31:G36)","v":92.790447595309956},null,null,0,null,{"$f":"SUM(L31:L36)","v":98.214285714285708},null,{"$f":"SUM(N31:N36)","v":93.925301306747158},null,null,0,null,100,null,{"$f":"SUM(U31:U36)","v":95.19633746355683},null,null,0,null,null,null,{"$f":"SUM(AB31:AB36)","v":96.61989795918366}]
[null,null,1,10,{"$f":"D31/(1+$G$26)^$C31","v":8.928571428571427},10,{"$f":"F31/(1+$G$26)^$C31","v":8.928571428571427}]
[null,null,2,110,{"$f":"D32/(1+$G$26)^$C32","v":87.69132653061223},10,7.9719387755102034,null,null,1,110,{"$f":"K32/(1+$N$26)^$J32","v":98.214285714285708},10,{"$f":"M32/(1+$N$26)^$J32","v":8.928571428571427}]
[null,null,3,null,null,10,7.1178024781341085,null,null,2,null,null,10,{"$f":"M33/(1+$N$26)^$J33","v":7.9719387755102034},null,null,1,null,null,10,{"$f":"T33/(1+U$26)^Q33","v":8.928571428571427}]
[null,null,4,null,null,10,6.3551807840483114,null,null,3,null,null,10,7.1178024781341085,null,null,2,null,null,10,{"$f":"T34/(1+U$26)^Q34","v":7.9719387755102034},null,null,1,null,null,10,{"$f":"AA34/(1+AB$26)^X34","v":8.928571428571427}]
[null,null,5,null,null,110,62.416954129045912,null,null,4,null,null,110,69.906988624531422,null,null,3,null,null,110,78.295827259475203,null,null,2,null,null,110,{"$f":"AA35/(1+AB$26)^X35","v":87.69132653061223}]
[]
[null,null,null,"Duration",{"$f":"SUMPRODUCT($C$31:$C$35,E31:E35)/E30","v":1.9075907590759074},"Duration",{"$f":"SUMPRODUCT($C$31:$C$35,G31:G35)/G30","v":4.1354617866377499},null,null,null,"Duration",{"$f":"SUMPRODUCT($J$32:$J$35,L32:L35)/L30","v":1},"Duration",{"$f":"SUMPRODUCT($J$32:$J$35,N32:N35)/N30","v":3.4692868309032727},null,null,null,"Duration",{"$f":"SUMPRODUCT($J$32:$J$35,S32:S35)/S30","v":0},"Duration",{"$f":"SUMPRODUCT($Q$32:$Q$35,U32:U35)/U30","v":2.7286756789089606}]
[null,null,null,"Weights",{"$f":"(E40-G37)/(E37-G37)","v":0.50966226167965689},null,{"$f":"1-E38","v":0.49033773832034311},null,null,null,"Weights",{"$f":"(L40-N37)/(L37-N37)","v":0.59502477092375816},null,{"$f":"1-L38","v":0.40497522907624184},null,null,null,"Weights",{"$f":"(S40-U37)/(S37-U37)","v":0.63352185540795303},null,{"$f":"1-S38","v":0.36647814459204697}]
[]
[null,null,null,"Desired duration",3,null,null,null,null,null,"Desired duration",2,null,null,null,null,null,"Desired duration",1,null,null,null,null,null,"Desired duration",0]
[null,null,null,"Effective duration",{"$f":"E37*E38+G37*G38","v":3},null,null,null,null,null,"Effective duration",{"$f":"L37*L38+N37*N38","v":2},null,null,null,null,null,"Effective duration",1,null,null,null,null,null,"Effective duration"]
[null,null,null,"Amount available?",10000,null,null,null,null,null,"Amount available?",{"$f":"E45*(10+L30)+G45*(10+N30)","v":11200},{"$f":"L42/E42-1","v":0.12000000000000011},null,null,null,null,"Amount available?",{"$f":"L45*(10+S30)+N45*(10+U30)","v":12544},{"$f":"S42/L42-1","v":0.12000000000000011},null,null,null,null,"Amount available?",{"$f":"U45*(10+AB30)+S44*(1+AB26)","v":14049.280000000002},{"$f":"(Z42/E42)^(1/3)-1","v":0.12000000000000011}]
[]
[null,null,null,"Allocation",{"$f":"E42*E38","v":5096.6226167965688},"EUR",{"$f":"E42*G38","v":4903.3773832034312},"EUR",null,null,"Allocation",{"$f":"L42*L38","v":6664.2774343460915},"EUR",{"$f":"L42*N38","v":4535.7225656539085},"EUR",null,null,"Allocation",{"$f":"S38*S42","v":7946.8981542373631},"EUR",{"$f":"S42*U38","v":4597.1018457626369},"EUR",null,null,"Average yearly return over 3y"]
[null,null,null,"# bonds of 1000?",{"$f":"E44/E30","v":52.749203057010035},null,{"$f":"G44/G30","v":52.843557826002659},{"$f":"GetFormulaNumbers(G45)","v":"=4'903.38/92.79"},null,null,"# bonds of 1000?",{"$f":"L44/L30","v":67.854461149705656},null,{"$f":"N44/N30","v":48.290742776974014},null,null,null,"# bonds of 1000?",null,null,{"$f":"U44/U30","v":48.290742776974007}]
[]
[]
[]
[null,3,"Stress-test"]
[null,null,null,null,"Rate at year 2"]
[null,null,null,{"$f":"AA42","v":0.12000000000000011},0.02,0.1,0.11,0.13,0.15]
[null,null,"rate at year 1",0.09,0.12487339337780146,0.12045124805209051,0.12045232065263356,0.12078624136269145,0.12154235298648919]
[null,null,null,0.1,0.12516759257253041,0.12024881453509084,0.12019252295039484,0.12041455770832066,0.12106240580360006]
[null,null,null,0.11,0.1255895909933562,0.12017517576135628,0.12006163716033735,0.12017201884035678,0.12071183609609681]
[null,null,null,0.12,0.12613590653045437,0.12022684660277072,0.12005617670226543,0.12005513450673644,0.12048714890162748]
[null,null,null,0.15,0.12848764485670294,0.12110060112028442,0.1207592212534474,0.12042525481278976,0.12053519101617627]